
// <copyright file="ExcelHelper.cs" company="Subhadra Software Solutions">
//   
// </copyright>
// <summary>
//   The excel helper.
// </summary>

namespace SubhadraSolutions.Sharp.Utils.Data.Helpers
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Linq;

    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;

    using SubhadraSolutions.Sharp.Utils.Reflection;

    /// <summary>
    /// The excel helper.
    /// </summary>
    public static class ExcelHelper
    {
        #region Public Methods and Operators

        /// <summary>
        /// The append.
        /// </summary>
        /// <param name="sourceSheet">
        /// The source sheet.
        /// </param>
        /// <param name="sourceSharedStringTablePart">
        /// The source shared string table part.
        /// </param>
        /// <param name="targetSheetData">
        /// The target sheet data.
        /// </param>
        /// <param name="numberOfRowsToSkip">
        /// The number of rows to skip.
        /// </param>
        public static void Append(Worksheet sourceSheet, SharedStringTablePart sourceSharedStringTablePart, SheetData targetSheetData, int numberOfRowsToSkip = 0)
        {
            int targetSheetRowsCount = targetSheetData.ChildElements.Count;
            IEnumerable<Row> dataRows = from row in sourceSheet.Descendants<Row>() where row.RowIndex > numberOfRowsToSkip select row;
            int rowIndex = targetSheetRowsCount + 1;
            foreach (Row row in dataRows)
            {
                var newRow = new Row();
                IEnumerable<Cell> cells = from cell in row.Descendants<Cell>() select cell;
                bool isEmpty = true;
                foreach (Cell cell in cells)
                {
                    string cellValue = null;
                    if (cell.CellValue != null)
                    {
                        if (cell.DataType != null && cell.DataType == CellValues.SharedString)
                        {
                            int n;
                            cellValue = int.TryParse(cell.CellValue.InnerText, out n) ? sourceSharedStringTablePart.SharedStringTable.ChildElements[n].InnerText : cell.CellValue.InnerText;
                        }
                        else
                        {
                            cellValue = cell.CellValue.Text;
                        }
                    } 

                    // var cellValue =
                    // (cell.CellValue != null
                    // ? (Int32.TryParse(cell.CellValue.InnerText, out n)
                    // ? (cell.DataType != null && cell.DataType == CellValues.SharedString
                    // ? sourceSharedStringTablePart.SharedStringTable.ChildElements[n].InnerText
                    // : cell.CellValue.InnerText)
                    // : cell.CellValue.Text)
                    // : null);
                    // cellValue = StringHelper.Trim(cellValue);
                    if (cellValue != null && cellValue != "#N/A")
                    {
                        isEmpty = false;
                    }

                    var newCell = new Cell { DataType = (cell.DataType == null || cell.DataType == CellValues.SharedString) ? CellValues.String : cell.DataType.Value, CellValue = new CellValue(cellValue) };

                    // var newCell = (Cell)cell.CloneNode(false);
                    // newCell.DataType = (cell.DataType == null || cell.DataType == CellValues.SharedString) ? CellValues.String : cell.DataType.Value;
                    // newCell.CellValue = new CellValue(cellValue);
                    string newCellReference = cell.CellReference.Value;
                    int ind = 0;
                    while (!char.IsDigit(newCellReference[ind]))
                    {
                        ind++;
                    }

                    newCellReference = newCellReference.Substring(0, ind) + rowIndex;

                    // newCell.CellReference = Regex.Replace(cell.CellReference.Value, @"\d", "") + rowIndex.ToString();
                    newCell.CellReference = newCellReference;
                    newRow.AppendChild(newCell);
                }

                if (!isEmpty)
                {
                    targetSheetData.AppendChild(newRow);
                    rowIndex++;
                }
            }
        }

        /// <summary>
        /// The append.
        /// </summary>
        /// <param name="sourceFileName">
        /// The source file name.
        /// </param>
        /// <param name="targetFileName">
        /// The target file name.
        /// </param>
        /// <param name="sheetInfos">
        /// The sheet infos.
        /// </param>
        public static void Append(string sourceFileName, string targetFileName, IList<KeyValuePair<string, int>> sheetInfos)
        {
            var openSettings = new OpenSettings { AutoSave = false };
            using (SpreadsheetDocument targetSpreadSheet = SpreadsheetDocument.Open(targetFileName, true, openSettings))
            {
                using (SpreadsheetDocument sourceSpreadSheet = SpreadsheetDocument.Open(sourceFileName, false))
                {
                    var targetWorksheets = new List<Worksheet>();

                    WorkbookPart sourceWorkbookPart = sourceSpreadSheet.WorkbookPart;

                    IEnumerable<Sheet> sourceSheets = sourceWorkbookPart.Workbook.Descendants<Sheet>();

                    SharedStringTablePart sourceSharedStringTablePart = sourceWorkbookPart.SharedStringTablePart;

                    WorkbookPart targetWorkbookPart = targetSpreadSheet.WorkbookPart;
                    IEnumerable<Sheet> targetSheets = targetWorkbookPart.Workbook.Descendants<Sheet>();
                    foreach (var kvp in sheetInfos)
                    {
                        Sheet sourceSheet = sourceSheets.SingleOrDefault(s => s.Name == kvp.Key);
                        var sourceWorksheetPart = (WorksheetPart)sourceWorkbookPart.GetPartById(sourceSheet.Id);

                        Sheet targetSheet = targetSheets.SingleOrDefault(s => s.Name == kvp.Key);

                        var targetWorksheetPart = (WorksheetPart)targetWorkbookPart.GetPartById(targetSheet.Id);

                        var targetSheetData = targetWorksheetPart.Worksheet.GetFirstChild<SheetData>();

                        // var sourceSheetData = sourceWorksheetPart.Worksheet.GetFirstChild<SheetData>();
                        Append(sourceWorksheetPart.Worksheet, sourceSharedStringTablePart, targetSheetData, kvp.Value);
                        targetWorksheets.Add(targetWorksheetPart.Worksheet);
                    }

                    foreach (Worksheet targetWorksheet in targetWorksheets)
                    {
                        targetWorksheet.Save();
                    }
                }
            }
        }

        /// <summary>
        /// The append data reader.
        /// </summary>
        /// <param name="reader">
        /// The reader.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        /// <param name="sheetName">
        /// The sheet name.
        /// </param>
        public static void AppendDataReader(IDataReader reader, string fileName, string sheetName)
        {
            using (FileStream stream = File.Open(fileName, FileMode.OpenOrCreate))
            {
                AppendDataReader(reader, stream, sheetName);
            }
        }

        /// <summary>
        /// The append data reader.
        /// </summary>
        /// <param name="reader">
        /// The reader.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        /// <param name="sheetName">
        /// The sheet name.
        /// </param>
        public static void AppendDataReader(IDataReader reader, Stream stream, string sheetName)
        {
            var adapter = new DataReaderDataTableAdapter(reader, sheetName);
            AppendDataTableOrDataReader(adapter, stream);
        }

        /// <summary>
        /// The append data set.
        /// </summary>
        /// <param name="ds">
        /// The ds.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        public static void AppendDataSet(DataSet ds, string fileName)
        {
            using (FileStream stream = File.Open(fileName, FileMode.OpenOrCreate))
            {
                AppendDataSet(ds, stream);
            }
        }

        /// <summary>
        /// The append data set.
        /// </summary>
        /// <param name="ds">
        /// The ds.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        public static void AppendDataSet(DataSet ds, Stream stream)
        {
            var tables = new DataTable[ds.Tables.Count];
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                tables[i] = ds.Tables[i];
            }

            AppendDataTables(tables, stream);
        }

        /// <summary>
        /// The append data table.
        /// </summary>
        /// <param name="table">
        /// The table.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        public static void AppendDataTable(DataTable table, Stream stream)
        {
            var adapter = new DataReaderDataTableAdapter(table);
            AppendDataTableOrDataReader(adapter, stream);
        }

        /// <summary>
        /// The append data table.
        /// </summary>
        /// <param name="table">
        /// The table.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        public static void AppendDataTable(DataTable table, string fileName)
        {
            using (FileStream stream = File.Open(fileName, FileMode.OpenOrCreate))
            {
                AppendDataTable(table, stream);
            }
        }

        /// <summary>
        /// The append data table or data reader.
        /// </summary>
        /// <param name="adapter">
        /// The adapter.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        public static void AppendDataTableOrDataReader(DataReaderDataTableAdapter adapter, string fileName)
        {
            using (FileStream stream = File.Open(fileName, FileMode.OpenOrCreate))
            {
                AppendDataTableOrDataReader(adapter, stream);
            }
        }

        /// <summary>
        /// The append data table or data reader.
        /// </summary>
        /// <param name="adapter">
        /// The adapter.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        public static void AppendDataTableOrDataReader(DataReaderDataTableAdapter adapter, Stream stream)
        {
            AppendDataTablesOrDataReaders(new[] { adapter }, stream);
        }

        /// <summary>
        /// The append data tables.
        /// </summary>
        /// <param name="dataTables">
        /// The data tables.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        public static void AppendDataTables(IEnumerable<DataTable> dataTables, string fileName)
        {
            using (FileStream stream = File.Open(fileName, FileMode.OpenOrCreate))
            {
                AppendDataTables(dataTables, stream);
            }
        }

        /// <summary>
        /// The append data tables.
        /// </summary>
        /// <param name="dataTables">
        /// The data tables.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        public static void AppendDataTables(IEnumerable<DataTable> dataTables, Stream stream)
        {
            AppendDataTablesOrDataReaders(buildAdapters(dataTables), stream);
        }

        /// <summary>
        /// The append data tables or data readers.
        /// </summary>
        /// <param name="adapters">
        /// The adapters.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        public static void AppendDataTablesOrDataReaders(IEnumerable<DataReaderDataTableAdapter> adapters, string fileName)
        {
            using (FileStream stream = File.Open(fileName, FileMode.OpenOrCreate))
            {
                AppendDataTablesOrDataReaders(adapters, stream);
            }
        }

        /// <summary>
        /// The append data tables or data readers.
        /// </summary>
        /// <param name="adapters">
        /// The adapters.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        public static void AppendDataTablesOrDataReaders(IEnumerable<DataReaderDataTableAdapter> adapters, Stream stream)
        {
            var openSettings = new OpenSettings { AutoSave = false };
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(stream, true, openSettings))
            {
                // foreach(var adapter in adapters)
                // {
                // var workbookPart = spreadSheet.WorkbookPart;
                // var ss =
                // workbookPart.Workbook.Descendants<Sheet>().SingleOrDefault(s => s.Name == adapter.Name);
                // WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Name);
                // var sharedStringTablePart = workbookPart.SharedStringTablePart;
                // var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                // appendRows(adapter, sheetData);
                // worksheets.Add(worksheetPart.Worksheet);
                // }
                // foreach (var worksheet in worksheets)
                // {
                // worksheet.Save();
                // }
                var worksheets = new List<Worksheet>();

                WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>();
                foreach (DataReaderDataTableAdapter adapter in adapters)
                {
                    Sheet ss = sheets.SingleOrDefault(s => s.Name == adapter.Name);
                    var worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
                    SharedStringTablePart sharedStringTablePart = workbookPart.SharedStringTablePart;
                    var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                    appendRows(adapter, sheetData);
                    worksheets.Add(worksheetPart.Worksheet);
                }

                foreach (Worksheet worksheet in worksheets)
                {
                    worksheet.Save();
                }
            }
        }

        /// <summary>
        /// The get cell value.
        /// </summary>
        /// <param name="cell">
        /// The cell.
        /// </param>
        /// <param name="stringTablePart">
        /// The string table part.
        /// </param>
        /// <returns>
        /// The <see cref="string"/>.
        /// </returns>
        public static string GetCellValue(Cell cell, SharedStringTablePart stringTablePart)
        {
            if (cell.ChildElements.Count == 0)
            {
                return null;
            }

            string value = cell.ElementAt(0).InnerText; // CellValue.InnerText;
            int val;
            if (int.TryParse(value, out val))
            {
                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                {
                    value = stringTablePart.SharedStringTable.ChildElements[val].InnerText;
                }
            }
            else
            {
                value = cell.CellValue.Text;
            }

            return value;
        }

        /// <summary>
        /// The get column names.
        /// </summary>
        /// <param name="worksheet">
        /// The worksheet.
        /// </param>
        /// <param name="sharedStringTablePart">
        /// The shared string table part.
        /// </param>
        /// <returns>
        /// The <see cref="List"/>.
        /// </returns>
        public static List<KeyValuePair<string, Type>> GetColumnNames(Worksheet worksheet, SharedStringTablePart sharedStringTablePart)
        {
            Row firstRow = worksheet.Descendants<Row>().FirstOrDefault();
            var columns = new List<KeyValuePair<string, Type>>();
            if (firstRow != null)
            {
                int i = 0;
                foreach (Cell c in firstRow.ChildElements)
                {
                    string value = GetCellValue(c, sharedStringTablePart);
                    value = StringHelper.Trim(value) ?? GetNthColumnName(i);

                    columns.Add(new KeyValuePair<string, Type>(value, typeof(string)));
                    i++;
                }
            }

            return columns;
        }

        /// <summary>
        /// The get column number.
        /// </summary>
        /// <param name="name">
        /// The name.
        /// </param>
        /// <returns>
        /// The <see cref="int"/>.
        /// </returns>
        public static int GetColumnNumber(string name)
        {
            int number = 0;
            int pow = 1;
            for (int i = name.Length - 1; i >= 0; i--)
            {
                number += (name[i] - 'A' + 1) * pow;
                pow *= 26;
            }

            return number;
        }

        /// <summary>
        /// The get mapping cell data type.
        /// </summary>
        /// <param name="t">
        /// The t.
        /// </param>
        /// <returns>
        /// The <see cref="CellValues"/>.
        /// </returns>
        public static CellValues GetMappingCellDataType(Type t)
        {
            if (t.IsNumericType())
            {
                return CellValues.Number;
            }

            if (t == typeof(bool) || t == typeof(bool?))
            {
                return CellValues.Boolean;
            }

            return CellValues.String;

            // if (t == typeof(DateTime) || t == typeof(IsNullable<DateTime>))
            // {
            // return CellValues.Date;
            // }
            // return CellValues.String;
        }

        /// <summary>
        /// The get mapping cell data types.
        /// </summary>
        /// <param name="adapter">
        /// The adapter.
        /// </param>
        /// <returns>
        /// The <see cref="CellValues[]"/>.
        /// </returns>
        public static CellValues[] GetMappingCellDataTypes(DataReaderDataTableAdapter adapter)
        {
            var dataTypes = new CellValues[adapter.ColumnCount];
            for (int i = 0; i < adapter.ColumnCount; i++)
            {
                Type t = adapter.GetColumnType(i);
                dataTypes[i] = GetMappingCellDataType(t);
            }

            return dataTypes;
        }

        /// <summary>
        /// The get nth column name.
        /// </summary>
        /// <param name="n">
        /// The n.
        /// </param>
        /// <returns>
        /// The <see cref="string"/>.
        /// </returns>
        public static string GetNthColumnName(int n)
        {
            string name = string.Empty;
            while (n > 0)
            {
                n--;
                name = (char)('A' + n % 26) + name;
                n /= 26;
            }

            return name;
        }

        /// <summary>
        /// The get rows count.
        /// </summary>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        /// <param name="sheetNames">
        /// The sheet names.
        /// </param>
        /// <returns>
        /// The <see cref="IEnumerable"/>.
        /// </returns>
        public static IEnumerable<KeyValuePair<string, int>> GetRowsCount(string fileName, IEnumerable<string> sheetNames)
        {
            using (SpreadsheetDocument sourceSpreadSheet = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart sourceWorkbookPart = sourceSpreadSheet.WorkbookPart;
                IEnumerable<Sheet> sourceSheets = sourceWorkbookPart.Workbook.Descendants<Sheet>();

                SharedStringTablePart sourceSharedStringTablePart = sourceWorkbookPart.SharedStringTablePart;

                foreach (string sheetName in sheetNames)
                {
                    Sheet sourceSheet = sourceSheets.SingleOrDefault(s => s.Name == sheetName);
                    var sourceWorksheetPart = (WorksheetPart)sourceWorkbookPart.GetPartById(sourceSheet.Id);
                    int rowsCount = sourceWorksheetPart.Worksheet.Descendants<Row>().Count();
                    yield return new KeyValuePair<string, int>(sheetName, rowsCount);
                }
            }
        }

        /// <summary>
        /// The get rows count excluding header.
        /// </summary>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        /// <param name="sheetName">
        /// The sheet name.
        /// </param>
        /// <returns>
        /// The <see cref="int"/>.
        /// </returns>
        public static int GetRowsCountExcludingHeader(string fileName, string sheetName)
        {
            using (FileStream fs = File.OpenRead(fileName))
            {
                return GetRowsCountExcludingHeader(fs, sheetName);
            }
        }

        /// <summary>
        /// The get rows count excluding header.
        /// </summary>
        /// <param name="stream">
        /// The stream.
        /// </param>
        /// <param name="sheetName">
        /// The sheet name.
        /// </param>
        /// <returns>
        /// The <see cref="int"/>.
        /// </returns>
        public static int GetRowsCountExcludingHeader(Stream stream, string sheetName)
        {
            SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(stream, false);
            WorkbookPart workbookPart = spreadSheet.WorkbookPart;
            WorksheetPart worksheetPart = null;
            Sheet ss = workbookPart.Workbook.Descendants<Sheet>().SingleOrDefault(s => s.Name == sheetName);
            worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
            int count = worksheetPart.Worksheet.Descendants<Row>().Count();
            return count - 1;
        }

        /// <summary>
        /// The get sheet names.
        /// </summary>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        /// <returns>
        /// The <see cref="List"/>.
        /// </returns>
        public static List<string> GetSheetNames(string fileName)
        {
            using (FileStream fs = File.OpenRead(fileName))
            {
                return GetSheetNames(fs);
            }
        }

        /// <summary>
        /// The get sheet names.
        /// </summary>
        /// <param name="stream">
        /// The stream.
        /// </param>
        /// <returns>
        /// The <see cref="List"/>.
        /// </returns>
        public static List<string> GetSheetNames(Stream stream)
        {
            var names = new List<string>();
            SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(stream, false);
            WorkbookPart workbookPart = spreadSheet.WorkbookPart;
            IEnumerable<Sheet> ss = workbookPart.Workbook.Descendants<Sheet>();
            foreach (Sheet s in ss)
            {
                names.Add(s.Name);
            }

            return names;
        }

        /// <summary>
        /// The read sheet to data reader.
        /// </summary>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        /// <param name="sheetName">
        /// The sheet name.
        /// </param>
        /// <returns>
        /// The <see cref="IDataReader"/>.
        /// </returns>
        public static IDataReader ReadSheetToDataReader(string fileName, string sheetName)
        {
            using (FileStream fs = File.OpenRead(fileName))
            {
                return ReadSheetToDataReader(fs, sheetName);
            }
        }

        /// <summary>
        /// The read sheet to data reader.
        /// </summary>
        /// <param name="stream">
        /// The stream.
        /// </param>
        /// <param name="sheetName">
        /// The sheet name.
        /// </param>
        /// <returns>
        /// The <see cref="IDataReader"/>.
        /// </returns>
        public static IDataReader ReadSheetToDataReader(Stream stream, string sheetName)
        {
            IDataReader[] readers = ReadSheetsToDataReaders(stream, new[] { sheetName });
            return readers[0];
        }

        /// <summary>
        /// The read sheet to data table.
        /// </summary>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        /// <param name="sheetName">
        /// The sheet name.
        /// </param>
        /// <returns>
        /// The <see cref="DataTable"/>.
        /// </returns>
        public static DataTable ReadSheetToDataTable(string fileName, string sheetName)
        {
            using (FileStream fs = File.OpenRead(fileName))
            {
                return ReadSheetToDataTable(fs, sheetName);
            }
        }

        /// <summary>
        /// The read sheet to data table.
        /// </summary>
        /// <param name="stream">
        /// The stream.
        /// </param>
        /// <param name="sheetName">
        /// The sheet name.
        /// </param>
        /// <returns>
        /// The <see cref="DataTable"/>.
        /// </returns>
        public static DataTable ReadSheetToDataTable(Stream stream, string sheetName)
        {
            DataTable[] tables = ReadSheetsToDataTables(stream, new[] { sheetName });
            return tables[0];
        }

        /// <summary>
        /// The read sheets to data readers.
        /// </summary>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        /// <param name="sheetNames">
        /// The sheet names.
        /// </param>
        /// <returns>
        /// The <see cref="IDataReader[]"/>.
        /// </returns>
        public static IDataReader[] ReadSheetsToDataReaders(string fileName, params string[] sheetNames)
        {
            using (FileStream fs = File.OpenRead(fileName))
            {
                return ReadSheetsToDataReaders(fs, sheetNames);
            }
        }

        /// <summary>
        /// The read sheets to data readers.
        /// </summary>
        /// <param name="stream">
        /// The stream.
        /// </param>
        /// <param name="sheetNames">
        /// The sheet names.
        /// </param>
        /// <returns>
        /// The <see cref="IDataReader[]"/>.
        /// </returns>
        public static IDataReader[] ReadSheetsToDataReaders(Stream stream, params string[] sheetNames)
        {
            SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(stream, false);
            WorkbookPart workbookPart = spreadSheet.WorkbookPart;
            IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>();
            var readers = new IDataReader[sheetNames.Length];
            for (int i = 0; i < sheetNames.Length; i++)
            {
                Sheet ss = sheets.SingleOrDefault(s => s.Name == sheetNames[i]);
                var worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
                SharedStringTablePart sharedStringTablePart = workbookPart.SharedStringTablePart;

                var strategy = new ExcelSheetDataReaderStrategy(worksheetPart.Worksheet, sharedStringTablePart);
                strategy.Initialize();
                readers[i] = new GenericDataReader(strategy);
            }

            return readers;
        }

        /// <summary>
        /// The read sheets to data tables.
        /// </summary>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        /// <param name="sheetNames">
        /// The sheet names.
        /// </param>
        /// <returns>
        /// The <see cref="DataTable[]"/>.
        /// </returns>
        public static DataTable[] ReadSheetsToDataTables(string fileName, params string[] sheetNames)
        {
            using (FileStream fs = File.OpenRead(fileName))
            {
                return ReadSheetsToDataTables(fs, sheetNames);
            }
        }

        /// <summary>
        /// The read sheets to data tables.
        /// </summary>
        /// <param name="stream">
        /// The stream.
        /// </param>
        /// <param name="sheetNames">
        /// The sheet names.
        /// </param>
        /// <returns>
        /// The <see cref="DataTable[]"/>.
        /// </returns>
        public static DataTable[] ReadSheetsToDataTables(Stream stream, params string[] sheetNames)
        {
            IDataReader[] readers = ReadSheetsToDataReaders(stream, sheetNames);
            var tables = new DataTable[sheetNames.Length];
            for (int i = 0; i < sheetNames.Length; i++)
            {
                var table = new DataTable(sheetNames[i]);
                table.Load(new FilteredDataReader(readers[i], EmptyRecordFilter.Instance));
                tables[i] = table;
            }

            return tables;
        }

        /// <summary>
        /// The write data reader.
        /// </summary>
        /// <param name="reader">
        /// The reader.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        /// <param name="sheetName">
        /// The sheet name.
        /// </param>
        public static void WriteDataReader(IDataReader reader, string fileName, string sheetName)
        {
            using (FileStream stream = File.Create(fileName))
            {
                WriteDataReader(reader, stream, sheetName);
            }
        }

        /// <summary>
        /// The write data reader.
        /// </summary>
        /// <param name="reader">
        /// The reader.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        /// <param name="sheetName">
        /// The sheet name.
        /// </param>
        public static void WriteDataReader(IDataReader reader, Stream stream, string sheetName)
        {
            var adapter = new DataReaderDataTableAdapter(reader, sheetName);
            WriteDataTableOrDataReader(adapter, stream);
        }

        /// <summary>
        /// The write data set.
        /// </summary>
        /// <param name="ds">
        /// The ds.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        public static void WriteDataSet(DataSet ds, string fileName)
        {
            using (FileStream stream = File.Create(fileName))
            {
                WriteDataSet(ds, stream);
            }
        }

        /// <summary>
        /// The write data set.
        /// </summary>
        /// <param name="ds">
        /// The ds.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        public static void WriteDataSet(DataSet ds, Stream stream)
        {
            var tables = new DataTable[ds.Tables.Count];
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                tables[i] = ds.Tables[i];
            }

            WriteDataTables(tables, stream);
        }

        /// <summary>
        /// The write data table.
        /// </summary>
        /// <param name="table">
        /// The table.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        public static void WriteDataTable(DataTable table, Stream stream)
        {
            var adapter = new DataReaderDataTableAdapter(table);
            WriteDataTableOrDataReader(adapter, stream);
        }

        /// <summary>
        /// The write data table.
        /// </summary>
        /// <param name="table">
        /// The table.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        public static void WriteDataTable(DataTable table, string fileName)
        {
            using (FileStream stream = File.Create(fileName))
            {
                WriteDataTable(table, stream);
            }
        }

        /// <summary>
        /// The write data table or data reader.
        /// </summary>
        /// <param name="adapter">
        /// The adapter.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        public static void WriteDataTableOrDataReader(DataReaderDataTableAdapter adapter, string fileName)
        {
            using (FileStream stream = File.Create(fileName))
            {
                WriteDataTableOrDataReader(adapter, stream);
            }
        }

        /// <summary>
        /// The write data table or data reader.
        /// </summary>
        /// <param name="adapter">
        /// The adapter.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        public static void WriteDataTableOrDataReader(DataReaderDataTableAdapter adapter, Stream stream)
        {
            WriteDataTablesOrDataReaders(new[] { adapter }, stream);
        }

        /// <summary>
        /// The write data tables.
        /// </summary>
        /// <param name="dataTables">
        /// The data tables.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        public static void WriteDataTables(IEnumerable<DataTable> dataTables, string fileName)
        {
            using (FileStream stream = File.Create(fileName))
            {
                WriteDataTables(dataTables, stream);
            }
        }

        /// <summary>
        /// The write data tables.
        /// </summary>
        /// <param name="dataTables">
        /// The data tables.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        public static void WriteDataTables(IEnumerable<DataTable> dataTables, Stream stream)
        {
            WriteDataTablesOrDataReaders(buildAdapters(dataTables), stream);
        }

        /// <summary>
        /// The write data tables or data readers.
        /// </summary>
        /// <param name="adapters">
        /// The adapters.
        /// </param>
        /// <param name="fileName">
        /// The file name.
        /// </param>
        public static void WriteDataTablesOrDataReaders(IEnumerable<DataReaderDataTableAdapter> adapters, string fileName)
        {
            using (FileStream stream = File.Create(fileName))
            {
                WriteDataTablesOrDataReaders(adapters, stream);
            }
        }

        /// <summary>
        /// The write data tables or data readers.
        /// </summary>
        /// <param name="adapters">
        /// The adapters.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        public static void WriteDataTablesOrDataReaders(IEnumerable<DataReaderDataTableAdapter> adapters, Stream stream)
        {
            using (SpreadsheetDocument workbook = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = workbook.AddWorkbookPart();
                workbook.WorkbookPart.Workbook = new Workbook { Sheets = new Sheets() };
                uint sheetId = 1;

                foreach (DataReaderDataTableAdapter adapter in adapters)
                {
                    var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                    var sheetData = new SheetData();
                    sheetPart.Worksheet = new Worksheet(sheetData);

                    var sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                    if (sheets.Elements<Sheet>().Any())
                    {
                        sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    var sheet = new Sheet { Id = relationshipId, SheetId = sheetId, Name = adapter.Name };
                    sheets.Append(sheet);

                    var headerRow = new Row();

                    var columns = new List<string>();
                    for (int i = 0; i < adapter.ColumnCount; i++)
                    {
                        string columnName = adapter.GetColumnName(i);
                        columns.Add(columnName);

                        var cell = new Cell { DataType = CellValues.String, CellValue = new CellValue(columnName) };
                        headerRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(headerRow);

                    appendRows(adapter, sheetData);
                }
            }
        }

        #endregion Public Methods and Operators

        #region Methods

        /// <summary>
        /// The append rows.
        /// </summary>
        /// <param name="adapter">
        /// The adapter.
        /// </param>
        /// <param name="parent">
        /// The parent.
        /// </param>
        private static void appendRows(DataReaderDataTableAdapter adapter, OpenXmlCompositeElement parent)
        {
            CellValues[] dataTypes = GetMappingCellDataTypes(adapter);

            while (adapter.Read())
            {
                var newRow = new Row();
                for (int i = 0; i < adapter.ColumnCount; i++)
                {
                    var cell = new Cell { DataType = dataTypes[i] };
                    string s = StringHelper.Trim(adapter.GetValue(i));
                    cell.CellValue = new CellValue(s);
                    newRow.AppendChild(cell);
                }

                parent.AppendChild(newRow);
            }
        }

        /// <summary>
        /// The build adapters.
        /// </summary>
        /// <param name="tables">
        /// The tables.
        /// </param>
        /// <returns>
        /// The <see cref="IEnumerable"/>.
        /// </returns>
        private static IEnumerable<DataReaderDataTableAdapter> buildAdapters(IEnumerable<DataTable> tables)
        {
            foreach (DataTable table in tables)
            {
                yield return new DataReaderDataTableAdapter(table);
            }
        }

        #endregion Methods
    }
}